package com.xca.znb.database;

import static com.xca.znb.database.DataBaseHelper.COLUMN_AGE;
import static com.xca.znb.database.DataBaseHelper.COLUMN_CONCERN;
import static com.xca.znb.database.DataBaseHelper.COLUMN_LOCAL;
import static com.xca.znb.database.DataBaseHelper.COLUMN_NAME;
import static com.xca.znb.database.DataBaseHelper.COLUMN_PASSWORD;
import static com.xca.znb.database.DataBaseHelper.COLUMN_PAY_PASSWORD;
import static com.xca.znb.database.DataBaseHelper.COLUMN_PHONE;
import static com.xca.znb.database.DataBaseHelper.TABLE_USERS;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.xca.znb.entity.Account;
import com.xca.znb.entity.Cart;
import com.xca.znb.entity.Collect;
import com.xca.znb.entity.Feedback;
import com.xca.znb.entity.Foot;
import com.xca.znb.entity.Goods;
import com.xca.znb.entity.Local;
import com.xca.znb.entity.Message;
import com.xca.znb.entity.News;
import com.xca.znb.entity.NewsLetter;
import com.xca.znb.entity.Order;
import com.xca.znb.entity.Procurement;
import com.xca.znb.entity.Trends;
import com.xca.znb.entity.User;

import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    private DataBaseHelper databaseHelper;//用户信息表
    private NewsDBHelper newsDBHelper ;//新闻表
    private LocalDBHelper localDBHelper ;//地址表
    private ProcurementDBHelper procurementDBHelper ;//采购表
    private AccountDBHelper accountDBHelper ;//账单表
    private GoodsDBHelper goodsDBHelper ;//商品表
    private TrendsDBHelper trendsDBHelper ; //动态表
    private FeedbackDBHelper feedbackDBHelper ; //反馈表
    private NewsletterDBHelper newsletterDBHelper ;//通讯表
    private OrderDBHelper orderDBHelper ; //订单表
    private CollectDBHelper collectDBHelper ;//收藏表
    private CartDBHelper cartDBHelper ;
    private FootDBHelper footDBHelper ;


    public UserDAO(Context context) {
        databaseHelper = new DataBaseHelper(context);
        newsDBHelper = new NewsDBHelper(context);
        localDBHelper = new LocalDBHelper(context) ;
        procurementDBHelper = new ProcurementDBHelper(context) ;
        accountDBHelper = new AccountDBHelper(context) ;
        goodsDBHelper = new GoodsDBHelper(context) ;
        trendsDBHelper = new TrendsDBHelper(context) ;
        feedbackDBHelper = new FeedbackDBHelper(context) ;
        newsletterDBHelper = new NewsletterDBHelper(context);
        orderDBHelper = new OrderDBHelper(context);
        collectDBHelper = new CollectDBHelper(context) ;
        cartDBHelper = new CartDBHelper(context);
        footDBHelper = new FootDBHelper(context);
    }

    public boolean isUserExists(String phone) {
        SQLiteDatabase db = databaseHelper.getReadableDatabase();
        String[] columns = { COLUMN_PHONE };
        String selection = COLUMN_PHONE + " = ?";
        String[] selectionArgs = { phone };
        Cursor cursor = db.query(DataBaseHelper.TABLE_USERS, columns, selection,
                selectionArgs, null, null, null);
        boolean exists = cursor.getCount() > 0;
        cursor.close();
        db.close();
        return exists;
    }

    public void addUser(String phone, String password) {
        SQLiteDatabase db = databaseHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_PHONE, phone);
        values.put(COLUMN_PASSWORD, password); // Assume other fields are null or empty
        values.put(COLUMN_PAY_PASSWORD , "000000");
        db.insert(DataBaseHelper.TABLE_USERS, null, values);
        db.close();
    }

    @SuppressLint("Range")
    public User getUser(String phone){
        SQLiteDatabase db = databaseHelper.getReadableDatabase() ;
        Cursor cursor = db.query(TABLE_USERS , new String[]{COLUMN_NAME , COLUMN_PASSWORD ,
                        DataBaseHelper.COLUMN_CONCERN, COLUMN_AGE,
        COLUMN_PAY_PASSWORD , COLUMN_LOCAL} , COLUMN_PHONE + " = ? " ,
                new String[]{phone} , null , null , null , null);
        User user = null ;
        if (cursor != null && cursor.moveToFirst()){
            user = new User(
                    cursor.getString(cursor.getColumnIndex(COLUMN_NAME)),
                    cursor.getString(cursor.getColumnIndex(COLUMN_PASSWORD)),
                    cursor.getString(cursor.getColumnIndex(COLUMN_CONCERN)),
                    cursor.getString(cursor.getColumnIndex(COLUMN_AGE)),
                    cursor.getString(cursor.getColumnIndex(COLUMN_PAY_PASSWORD)),
                    cursor.getString(cursor.getColumnIndex(COLUMN_LOCAL))
            );
            cursor.close();
        }
        return user ;
    }

    public Cursor getAllUsers() {
        SQLiteDatabase db = databaseHelper.getReadableDatabase();
        String[] columns = {
                COLUMN_PHONE,
                COLUMN_NAME,
                COLUMN_PASSWORD,
                DataBaseHelper.COLUMN_CONCERN,
                COLUMN_AGE,
                COLUMN_PAY_PASSWORD,
                COLUMN_LOCAL
        };
        return db.query(DataBaseHelper.TABLE_USERS, columns, null,
                null, null, null, null);
    }

    /**
     * 验证提供的密码是否与数据库中存储的密码匹配。
     *
     * @param phone     用户的电话号码
     * @param password  提供的密码
     * @return 如果密码正确返回true，否则返回false
     */
    public boolean isValidPassword(String phone, String password) {
        SQLiteDatabase db = databaseHelper.getReadableDatabase();
        String[] columns = { COLUMN_PASSWORD };
        String selection = COLUMN_PHONE + " = ?";
        String[] selectionArgs = { phone };
        String storedPassword = null;
        boolean isValid = false;

        Cursor cursor = null;
        try {
            cursor = db.query(DataBaseHelper.TABLE_USERS, columns, selection, selectionArgs, null, null, null);
            if (cursor.moveToFirst()) {
                // 假设密码以明文存储，实际应用中应该使用散列值比较
                int passwordIndex = cursor.getColumnIndex(COLUMN_PASSWORD);
                storedPassword = cursor.getString(passwordIndex);
                isValid = password.equals(storedPassword);
            }
        } catch (Exception e) {
            // 处理可能的异常，例如数据库查询失败
            e.printStackTrace();
        } finally {
            // 确保游标被关闭
            if (cursor != null) {
                cursor.close();
            }
            // 关闭数据库
            db.close();
        }

        return isValid;
    }


    public Cursor getUserByPhone(String phone) {
        SQLiteDatabase db = databaseHelper.getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_USERS + " WHERE " + COLUMN_PHONE + " = ?";
        return db.rawQuery(query, new String[]{phone});
    }

    //修改密码
    public void updatePasswordByPhone(String phone , String newPassword) {
        SQLiteDatabase db = databaseHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_PASSWORD, newPassword);
        String selection = COLUMN_PHONE + " = ?";
        db.update(TABLE_USERS, values, selection, new String[]{phone});
    }
    // 修改支付密码
    public void updatePayPasswordByPhone(String phone, String newPayPassword) {
        SQLiteDatabase db = databaseHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_PAY_PASSWORD, newPayPassword);
        String selection = COLUMN_PHONE + " = ?";
        db.update(TABLE_USERS, values, selection, new String[]{phone});
    }

    //修改年龄
    public void updateAgeByPhone(String phone, String newAge) {
        SQLiteDatabase db = databaseHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_AGE, newAge);
        String selection = COLUMN_PHONE + " = ?";
        db.update(TABLE_USERS, values, selection, new String[]{phone});
    }

//修改姓名
    public void updateNameByPhone(String phone , String newName){
        SQLiteDatabase db = databaseHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(COLUMN_NAME , newName);
        String selection = COLUMN_PHONE + " = ? ";
        db.update(TABLE_USERS , values ,selection ,new String[]{phone}) ;
    }
    //修改地址
    public void updateLocalByPhone(String phone, String newLocal) {
        SQLiteDatabase db = databaseHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_LOCAL, newLocal);
        String selection = COLUMN_PHONE + " = ?";
        db.update(TABLE_USERS, values, selection, new String[]{phone});
    }
    public void updateConcernByPhone(String phone , String newAvatar){
        SQLiteDatabase db = databaseHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(COLUMN_CONCERN , newAvatar);
        String selection = COLUMN_PHONE + " = ?";
        db.update(TABLE_USERS , values ,selection ,new String[]{phone}) ;
    }





    //以下是关于农技学院新闻表的操作方法
    //添加文章
    public boolean addNews(String news_type , String first_title , String second_title , String imgPath ,
                           String inputText , String publisher , String time){
        SQLiteDatabase db = newsDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(NewsDBHelper.NEWS_TYPE , news_type);
        values.put(NewsDBHelper.NEWS_TITLE , first_title);
        values.put(NewsDBHelper.NEWS_SECOND_TITLE , second_title);
        values.put(NewsDBHelper.NEWS_IMG , imgPath);
        values.put(NewsDBHelper.NEWS_TEXT , inputText);
        values.put(NewsDBHelper.NEWS_PUBLISHER , publisher);
        values.put(NewsDBHelper.NEWS_TIME , time);
        long isAdd = db.insert(NewsDBHelper.TABLE_NEWS , null , values) ;
        return isAdd != -1;
    }

    @SuppressLint("Range")
    public List<News> getNewsByType(String type){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<News> newsList = new ArrayList<>() ;
        try {
            db = newsDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + NewsDBHelper.TABLE_NEWS +
                    " WHERE " + NewsDBHelper.NEWS_TYPE + " = ?" +
                    " ORDER BY  " + NewsDBHelper.NEWS_ID + " DESC " ;
            cursor = db.rawQuery(query , new String[]{type});
            while (cursor.moveToNext()){
                News news = new News() ;
                news.setId(cursor.getInt(cursor.getColumnIndex(NewsDBHelper.NEWS_ID)));
                news.setType(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TYPE)));
                news.setFirstTitle(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TITLE)));
                news.setSecondTitle(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_SECOND_TITLE)));
                news.setImg(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_IMG)));
                news.setTexts(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TEXT)));
                news.setPublisher(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_PUBLISHER)));
                news.setTime(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TIME)));
                newsList.add(news) ;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return newsList;
    }

    @SuppressLint("Range")
    public List<News> getNews(){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<News> newsList = new ArrayList<>() ;
        try {
            db = newsDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + NewsDBHelper.TABLE_NEWS +
                    " ORDER BY  " + NewsDBHelper.NEWS_ID + " DESC " ;
            cursor = db.rawQuery(query , null);
            while (cursor.moveToNext()){
                News news = new News() ;
                news.setId(cursor.getInt(cursor.getColumnIndex(NewsDBHelper.NEWS_ID)));
                news.setType(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TYPE)));
                news.setFirstTitle(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TITLE)));
                news.setSecondTitle(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_SECOND_TITLE)));
                news.setImg(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_IMG)));
                news.setTexts(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TEXT)));
                news.setPublisher(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_PUBLISHER)));
                news.setTime(cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TIME)));
                newsList.add(news) ;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return newsList;
    }

    @SuppressLint("Range")
    public News getNewsById(int id){
        SQLiteDatabase db = newsDBHelper.getReadableDatabase() ;
        Cursor cursor = db.query(NewsDBHelper.TABLE_NEWS , new String[]{NewsDBHelper.NEWS_ID , NewsDBHelper.NEWS_TYPE ,
                NewsDBHelper.NEWS_TITLE ,NewsDBHelper.NEWS_SECOND_TITLE , NewsDBHelper.NEWS_IMG , NewsDBHelper.NEWS_TEXT,
                NewsDBHelper.NEWS_PUBLISHER ,NewsDBHelper.NEWS_TIME}, NewsDBHelper.NEWS_ID + " = ?" ,
                new String[]{String.valueOf(id)},
                null , null , null , null);
        News news = null ;
        if (cursor != null && cursor.moveToFirst()){
            news = new News(
                    cursor.getInt(cursor.getColumnIndex(NewsDBHelper.NEWS_ID)),
                    cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TYPE)),
                    cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TITLE)),
                    cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_SECOND_TITLE)),
                    cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_IMG)),
                    cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TEXT)),
                    cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_PUBLISHER)),
                    cursor.getString(cursor.getColumnIndex(NewsDBHelper.NEWS_TIME))
            );
            cursor.close();
        }
        return news ;
    }


    //以下是关于地址表的操作方法
    //添加地址
    public boolean addLocal(String user_name , String user_phone , String local , String local_type , String loginPhone){
        SQLiteDatabase db = localDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(LocalDBHelper.LOCAL_USER_NAME , user_name);
        values.put(LocalDBHelper.LOCAL_USER_PHONE , user_phone);
        values.put(LocalDBHelper.LOCAL_ADDRESS , local);
        values.put(LocalDBHelper.LOCAL_TYPE , local_type);
        values.put(LocalDBHelper.LOCAL_LOGIN_PHONE , loginPhone);
        long isAdd = db.insert(LocalDBHelper.TABLE_LOCAL , null , values) ;
        return isAdd != -1 ;
    }
    //更改地址类型
    public void updateDefaultLocal(String login_phone){
        SQLiteDatabase db = localDBHelper.getWritableDatabase();
        ContentValues values = new ContentValues() ;
        values.put(LocalDBHelper.LOCAL_TYPE , "否");
        String selection = LocalDBHelper.LOCAL_LOGIN_PHONE + " = ? AND " + LocalDBHelper.LOCAL_TYPE + " = '是' ";
        db.update(LocalDBHelper.TABLE_LOCAL , values , selection , new String[]{login_phone});
    }
    //修改地址
    public void updateLocalById(int id , String name , String phone , String address , String type){
        SQLiteDatabase db = localDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(LocalDBHelper.LOCAL_USER_NAME , name);
        values.put(LocalDBHelper.LOCAL_USER_PHONE , phone);
        values.put(LocalDBHelper.LOCAL_ADDRESS , address);
        values.put(LocalDBHelper.LOCAL_TYPE , type);
        String selection = LocalDBHelper.LOCAL_ID + " = ? " ;
        db.update(LocalDBHelper.TABLE_LOCAL , values , selection , new String[]{String.valueOf(id)});
    }
//通过id获取地址
    @SuppressLint("Range")
    public Local getLocalById(int id){
        SQLiteDatabase db = localDBHelper.getReadableDatabase();
        Cursor cursor = db.query(LocalDBHelper.TABLE_LOCAL , new String[]{LocalDBHelper.LOCAL_ID ,
        LocalDBHelper.LOCAL_USER_NAME , LocalDBHelper.LOCAL_USER_PHONE , LocalDBHelper.LOCAL_ADDRESS ,
        LocalDBHelper.LOCAL_TYPE , LocalDBHelper.LOCAL_LOGIN_PHONE} , LocalDBHelper.LOCAL_ID + " = ? " ,
                new String[]{String.valueOf(id)} , null , null , null , null);
        Local local = null ;
        if (cursor != null && cursor.moveToFirst()){
            local = new Local(
                    cursor.getInt(cursor.getColumnIndex(LocalDBHelper.LOCAL_ID)),
                    cursor.getString(cursor.getColumnIndex(LocalDBHelper.LOCAL_USER_NAME)),
                    cursor.getString(cursor.getColumnIndex(LocalDBHelper.LOCAL_USER_PHONE)),
                    cursor.getString(cursor.getColumnIndex(LocalDBHelper.LOCAL_ADDRESS)),
                    cursor.getString(cursor.getColumnIndex(LocalDBHelper.LOCAL_TYPE)),
                    cursor.getString(cursor.getColumnIndex(LocalDBHelper.LOCAL_LOGIN_PHONE))
            );
            cursor.close();
        }
        return local ;
    }

    //获取默认地址Id
    public int getLocalDefaultId(String phone){
        SQLiteDatabase db = localDBHelper.getReadableDatabase();
        int dId = 0 ;
        String query = "SELECT " + LocalDBHelper.LOCAL_ID + " FROM " + LocalDBHelper.TABLE_LOCAL + " WHERE " +
                LocalDBHelper.LOCAL_LOGIN_PHONE + " = ? AND " + LocalDBHelper.LOCAL_TYPE + " = '是'";
        Cursor cursor = db.rawQuery(query , new String[]{phone});
        try {
            if (cursor.moveToFirst()){
                dId = cursor.getInt(0);
            }
        }finally {
            cursor.close();
            db.close();
        }
        return dId;
    }
    //通过phone查询符合条件的地址集
    @SuppressLint("Range")
    public List<Local> getLocalByPhone(String phone){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Local> localList = new ArrayList<>() ;
        try {
            db = localDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + LocalDBHelper.TABLE_LOCAL + " WHERE " +
                    LocalDBHelper.LOCAL_LOGIN_PHONE + " = ? ORDER BY " + LocalDBHelper.LOCAL_ID + " DESC " ;
            cursor = db.rawQuery(query , new String[]{phone});
            while (cursor.moveToNext()){
                Local local = new Local() ;
                local.setId(cursor.getInt(cursor.getColumnIndex(LocalDBHelper.LOCAL_ID)));
                local.setName(cursor.getString(cursor.getColumnIndex(LocalDBHelper.LOCAL_USER_NAME)));
                local.setPhone(cursor.getString(cursor.getColumnIndex(LocalDBHelper.LOCAL_USER_PHONE)));
                local.setAddress(cursor.getString(cursor.getColumnIndex(LocalDBHelper.LOCAL_ADDRESS)));
                local.setType(cursor.getString(cursor.getColumnIndex(LocalDBHelper.LOCAL_TYPE)));
                local.setLogin_phone(cursor.getString(cursor.getColumnIndex(LocalDBHelper.LOCAL_LOGIN_PHONE)));
                localList.add(local);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return localList ;
    }
    public void deleteLocalById(int id){
        SQLiteDatabase db = localDBHelper.getWritableDatabase() ;
        db.delete(LocalDBHelper.TABLE_LOCAL , LocalDBHelper.LOCAL_ID + " = ? " ,
                new String[]{String.valueOf(id)});
        db.close();
    }


    //以下是关于采购表的操作方法
    //发布采购功能
    public boolean addProcurement(String type , String variety , String need ,String unit , String origin , String cycle ,
                                  String delivery , String text , String phone){
        SQLiteDatabase db = procurementDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues();
        values.put(ProcurementDBHelper.PROCUREMENT_TYPE , type);
        values.put(ProcurementDBHelper.PROCUREMENT_VARIETY , variety);
        values.put(ProcurementDBHelper.PROCUREMENT_NEED , need);
        values.put(ProcurementDBHelper.PROCUREMENT_UNIT , unit);
        values.put(ProcurementDBHelper.PROCUREMENT_ORIGIN , origin);
        values.put(ProcurementDBHelper.PROCUREMENT_CYCLE , cycle);
        values.put(ProcurementDBHelper.PROCUREMENT_DELIVERY , delivery);
        values.put(ProcurementDBHelper.PROCUREMENT_TEXT , text);
        values.put(ProcurementDBHelper.PROCUREMENT_PHONE , phone);
        long isAdd = db.insert(ProcurementDBHelper.TABLE_PROCUREMENT , null , values) ;
        return isAdd != -1 ;
    }

    @SuppressLint("Range")
    public List<Procurement> getProcurementByPhone(String phone){
        SQLiteDatabase db = procurementDBHelper.getReadableDatabase();
        List<Procurement> procurementList = new ArrayList<>();
        String query = "SELECT * FROM " + ProcurementDBHelper.TABLE_PROCUREMENT + " WHERE " +
                ProcurementDBHelper.PROCUREMENT_PHONE + " = ? ORDER BY " +
                ProcurementDBHelper.PROCUREMENT_ID + " DESC ";
        Cursor cursor = db.rawQuery(query , new String[]{phone});
        while (cursor.moveToNext()){
            Procurement procurement = new Procurement();
            procurement.setId(cursor.getInt(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_ID)));
            procurement.setType(cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_TYPE)));
            procurement.setVariety(cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_VARIETY)));
            procurement.setNeed(cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_NEED)));
            procurement.setUnit(cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_UNIT)));
            procurement.setOrigin(cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_ORIGIN)));
            procurement.setCycle(cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_CYCLE)));
            procurement.setDelivery(cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_DELIVERY)));
            procurement.setText(cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_TEXT)));
            procurement.setPhone(cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_PHONE)));
            procurementList.add(procurement);
        }
        cursor.close();
        db.close();
        return procurementList ;
    }

    @SuppressLint("Range")
    public Procurement getProcurementById(int id){
        SQLiteDatabase db = procurementDBHelper.getReadableDatabase();
        Cursor cursor = db.query(ProcurementDBHelper.TABLE_PROCUREMENT , new String[]{
                ProcurementDBHelper.PROCUREMENT_ID , ProcurementDBHelper.PROCUREMENT_TYPE , ProcurementDBHelper.PROCUREMENT_VARIETY , ProcurementDBHelper.PROCUREMENT_NEED ,
                ProcurementDBHelper.PROCUREMENT_UNIT , ProcurementDBHelper.PROCUREMENT_ORIGIN ,ProcurementDBHelper.PROCUREMENT_CYCLE ,
                ProcurementDBHelper.PROCUREMENT_DELIVERY , ProcurementDBHelper.PROCUREMENT_TEXT , ProcurementDBHelper.PROCUREMENT_PHONE} ,
                ProcurementDBHelper.PROCUREMENT_ID + " = ? " , new String[]{String.valueOf(id)} , null , null , null , null);
        Procurement procurement = null ;
        if (cursor != null && cursor.moveToFirst()){
            procurement = new Procurement(
                    cursor.getInt(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_ID)),
                    cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_TYPE)),
                    cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_VARIETY)),
                    cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_NEED)),
                    cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_UNIT)),
                    cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_ORIGIN)),
                    cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_CYCLE)),
                    cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_DELIVERY)),
                    cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_TEXT)),
                    cursor.getString(cursor.getColumnIndex(ProcurementDBHelper.PROCUREMENT_PHONE))
            );
            cursor.close();
        }
        return procurement ;
    }

    public void deleteProcurementById(int id){
        SQLiteDatabase db = procurementDBHelper.getWritableDatabase() ;
        db.delete(ProcurementDBHelper.TABLE_PROCUREMENT , ProcurementDBHelper.PROCUREMENT_ID + " = ?" ,
                new String[]{String.valueOf(id)});
        db.close();
    }


    //以下是账户表的操作方法
    //添加数据
    public boolean addAccount(String phone , double num , String type , String time_day , String time_sec){
        SQLiteDatabase db = accountDBHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(AccountDBHelper.ACCOUNT_PHONE , phone);
        values.put(AccountDBHelper.ACCOUNT_NUM , num);
        values.put(AccountDBHelper.ACCOUNT_TYPE , type);
        values.put(AccountDBHelper.ACCOUNT_TIME_DAY , time_day);
        values.put(AccountDBHelper.ACCOUNT_TIME_SEC , time_sec);
        long isAdd = db.insert(AccountDBHelper.TABLE_ACCOUNT , null , values);
        return isAdd != -1 ;
    }

    public void insertAccount(String phone , double num , String type , String time_day , String time_sec){
        SQLiteDatabase db = accountDBHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(AccountDBHelper.ACCOUNT_PHONE , phone);
        values.put(AccountDBHelper.ACCOUNT_NUM , num);
        values.put(AccountDBHelper.ACCOUNT_TYPE , type);
        values.put(AccountDBHelper.ACCOUNT_TIME_DAY , time_day);
        values.put(AccountDBHelper.ACCOUNT_TIME_SEC , time_sec);
        db.insert(AccountDBHelper.TABLE_ACCOUNT , null , values);
    }

    //以倒序获取账单
    @SuppressLint("Range")
    public List<Account> getAccountByPhone(String phone){
        SQLiteDatabase db = accountDBHelper.getReadableDatabase() ;
        List<Account> accounts = new ArrayList<>() ;
        String query = "SELECT * FROM " + AccountDBHelper.TABLE_ACCOUNT + " WHERE " +
        AccountDBHelper.ACCOUNT_PHONE + " = ? " + "ORDER BY " + AccountDBHelper.ACCOUNT_TIME_SEC + " DESC" ;

        Cursor cursor = db.rawQuery(query , new String[]{phone});
        while (cursor.moveToNext()){
            Account account = new Account();
            account.setNum(cursor.getDouble(cursor.getColumnIndex(AccountDBHelper.ACCOUNT_NUM)));
            account.setType(cursor.getString(cursor.getColumnIndex(AccountDBHelper.ACCOUNT_TYPE)));
            account.setTime_sec(cursor.getString(cursor.getColumnIndex(AccountDBHelper.ACCOUNT_TIME_SEC)));
            accounts.add(account) ;
        }
        cursor.close();
        db.close();

        return accounts ;
    }
    //在我的资产页面的查询方法
    public double getSum(String phone){
        SQLiteDatabase db = accountDBHelper.getReadableDatabase();
        double totalAmount = 0;
        String query = "SELECT SUM(CASE " +
                "WHEN " + AccountDBHelper.ACCOUNT_TYPE + " IN ('充值', '出售', '退款') THEN " + AccountDBHelper.ACCOUNT_NUM + " " +
                "WHEN " + AccountDBHelper.ACCOUNT_TYPE + " IN ('提现', '购买', '买家退款') THEN -" + AccountDBHelper.ACCOUNT_NUM + " " +
                "END) AS total_sum " +
                "FROM " + AccountDBHelper.TABLE_ACCOUNT +
                " WHERE " + AccountDBHelper.ACCOUNT_PHONE + " = ?";

        Cursor cursor = db.rawQuery(query, new String[]{phone});
        try {
            if (cursor.moveToFirst()) {
                totalAmount = cursor.getDouble(0);
            }
        } finally {
            cursor.close();
            db.close();
        }

        return totalAmount;
    }
    //在我的商店查询方法
    //今日销售
    public double getTodaySale(String phone, String time_day) {
        double sum = 0;
        SQLiteDatabase db = accountDBHelper.getReadableDatabase();
        String query = "SELECT SUM(CASE WHEN " + AccountDBHelper.ACCOUNT_TYPE + " = '出售' THEN " +
                AccountDBHelper.ACCOUNT_NUM + " ELSE CASE WHEN " + AccountDBHelper.ACCOUNT_TYPE + " = '买家退款' " +
                "THEN -" + AccountDBHelper.ACCOUNT_NUM + " END END) " +
                "FROM " + AccountDBHelper.TABLE_ACCOUNT +
                " WHERE " + AccountDBHelper.ACCOUNT_PHONE + " = ? AND " +
                AccountDBHelper.ACCOUNT_TIME_DAY + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{phone, time_day});
        try {
            if (cursor.getCount() > 0 && cursor.moveToFirst()) {
                sum = cursor.getDouble(0);
            }
        }finally {
            cursor.close();
            db.close();
        }

        return sum;
    }

    //获取月销售额
    public double getMonthSale(String phone, String time_month) {
        double sum = 0;
        String query = "SELECT SUM(CASE WHEN " + AccountDBHelper.ACCOUNT_TYPE + " = '出售' THEN " +
                AccountDBHelper.ACCOUNT_NUM + " ELSE CASE WHEN " + AccountDBHelper.ACCOUNT_TYPE + " = '买家退款' " +
                "THEN -" + AccountDBHelper.ACCOUNT_NUM + " END END) " +
                "FROM " + AccountDBHelper.TABLE_ACCOUNT +
                " WHERE " + AccountDBHelper.ACCOUNT_PHONE + " = ? AND " +
                AccountDBHelper.ACCOUNT_TIME_DAY + " LIKE ?";

        try (SQLiteDatabase db = accountDBHelper.getReadableDatabase();
             Cursor cursor = db.rawQuery(query, new String[]{phone, time_month + "%"})) {

            if (cursor.getCount() > 0 && cursor.moveToFirst()) {
                sum = cursor.getDouble(0);
            }
        } catch (Exception e) {
            // 异常处理，例如记录日志
            Log.e("DBError", "Error in getMonthSale: ", e);
        }

        return sum;
    }

    public double getTotalSale(String phone ){
        double sum = 0 ;
        SQLiteDatabase db = accountDBHelper.getReadableDatabase();
        String query = "SELECT SUM(CASE WHEN " + AccountDBHelper.ACCOUNT_TYPE + " = '出售' THEN " +
                AccountDBHelper.ACCOUNT_NUM + " ELSE CASE WHEN " + AccountDBHelper.ACCOUNT_TYPE + " = '买家退款' " +
                "THEN -" + AccountDBHelper.ACCOUNT_NUM + " END END) " +
                "FROM " + AccountDBHelper.TABLE_ACCOUNT +
                " WHERE " + AccountDBHelper.ACCOUNT_PHONE + " = ? ";
        Cursor cursor = db.rawQuery(query , new String[]{phone});

        try {
            if (cursor.getCount() > 0 && cursor.moveToFirst()){
                sum = cursor.getDouble(0);
            }
        }finally {
            cursor.close();
            db.close();
        }
        return sum ;
    }


    //以下是关于商品表的操作方法
    //添加商品
    public boolean addGoods(String name , String first_image , String img_list , String type , double price ,
                            String unit , String freight , String address , String text , String publisher , String state){
        SQLiteDatabase db = goodsDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(GoodsDBHelper.GOODS_NAME , name);
        values.put(GoodsDBHelper.GOODS_FIRST_IMG , first_image);
        values.put(GoodsDBHelper.GOODS_IMG_LIST , img_list);
        values.put(GoodsDBHelper.GOODS_TYPE , type);
        values.put(GoodsDBHelper.GOODS_PRICE , price);
        values.put(GoodsDBHelper.GOODS_UNIT , unit);
        values.put(GoodsDBHelper.GOODS_FREIGHT , freight);
        values.put(GoodsDBHelper.GOODS_ADDRESS , address);
        values.put(GoodsDBHelper.GOODS_TEXT , text);
        values.put(GoodsDBHelper.GOODS_PUBLISHER , publisher);
        values.put(GoodsDBHelper.GOODS_STATE , state);
        long isAdd = db.insert(GoodsDBHelper.TABLE_GOODS , null , values) ;
        return isAdd != -1 ;
    }

    public void updateGoodsByIdAndState(int id , String state){
        SQLiteDatabase db = goodsDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues();
        values.put(GoodsDBHelper.GOODS_STATE , state);
        String selection = GoodsDBHelper.GOODS_ID + " = ? ";
        db.update(GoodsDBHelper.TABLE_GOODS , values , selection , new String[]{String.valueOf(id)});
    }

    public void deleteGoodsById(int id){
        SQLiteDatabase db = goodsDBHelper.getWritableDatabase() ;
        db.delete(GoodsDBHelper.TABLE_GOODS , GoodsDBHelper.GOODS_ID + " = ? " ,
                new String[]{String.valueOf(id)});
        db.close();
    }
    //获取所有上架中商品
    @SuppressLint("Range")
    public List<Goods> getGoods(){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Goods> goodsList = new ArrayList<>() ;
        try {
            db = goodsDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + GoodsDBHelper.TABLE_GOODS + " WHERE " + GoodsDBHelper.GOODS_STATE + " = '上架中' ORDER BY " + GoodsDBHelper.GOODS_ID + " DESC " ;
            cursor = db.rawQuery(query , null);
            while (cursor.moveToNext()){
                Goods goods = new Goods();
            goods.setId(cursor.getInt(cursor.getColumnIndex(GoodsDBHelper.GOODS_ID)));
            goods.setName(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_NAME)));
            goods.setFirst_image(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FIRST_IMG)));
            goods.setImg_list(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_IMG_LIST)));
            goods.setType(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TYPE)));
            goods.setPrice(cursor.getDouble(cursor.getColumnIndex(GoodsDBHelper.GOODS_PRICE)));
            goods.setUnit(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_UNIT)));
            goods.setFreight(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FREIGHT)));
            goods.setAddress(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_ADDRESS)));
            goods.setText(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TEXT)));
            goods.setPublisher(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_PUBLISHER)));
            goods.setState(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_STATE)));
            goodsList.add(goods);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return goodsList ;
    }

    @SuppressLint("Range")
    public List<Goods> getGoodsOnByPhone(String phone){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Goods> goodsList = new ArrayList<>() ;
        try {
            db = goodsDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + GoodsDBHelper.TABLE_GOODS + " WHERE " + GoodsDBHelper.GOODS_PUBLISHER + " = ? AND " + GoodsDBHelper.GOODS_STATE + " = '上架中' ORDER BY " + GoodsDBHelper.GOODS_ID + " DESC " ;
            cursor = db.rawQuery(query , new String[]{phone});
            while (cursor.moveToNext()){
                Goods goods = new Goods();
                goods.setId(cursor.getInt(cursor.getColumnIndex(GoodsDBHelper.GOODS_ID)));
                goods.setName(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_NAME)));
                goods.setFirst_image(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FIRST_IMG)));
                goods.setImg_list(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_IMG_LIST)));
                goods.setType(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TYPE)));
                goods.setPrice(cursor.getDouble(cursor.getColumnIndex(GoodsDBHelper.GOODS_PRICE)));
                goods.setUnit(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_UNIT)));
                goods.setFreight(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FREIGHT)));
                goods.setAddress(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_ADDRESS)));
                goods.setText(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TEXT)));
                goods.setPublisher(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_PUBLISHER)));
                goods.setState(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_STATE)));
                goodsList.add(goods);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return goodsList ;
    }

    @SuppressLint("Range")
    public List<Goods> getGoodsOffByPhone(String phone){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Goods> goodsList = new ArrayList<>() ;
        try {
            db = goodsDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + GoodsDBHelper.TABLE_GOODS + " WHERE " + GoodsDBHelper.GOODS_PUBLISHER + " = ? AND " + GoodsDBHelper.GOODS_STATE + " = '已下架' ORDER BY " + GoodsDBHelper.GOODS_ID + " DESC " ;
            cursor = db.rawQuery(query , new String[]{phone});
            while (cursor.moveToNext()){
                Goods goods = new Goods();
                goods.setId(cursor.getInt(cursor.getColumnIndex(GoodsDBHelper.GOODS_ID)));
                goods.setName(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_NAME)));
                goods.setFirst_image(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FIRST_IMG)));
                goods.setImg_list(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_IMG_LIST)));
                goods.setType(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TYPE)));
                goods.setPrice(cursor.getDouble(cursor.getColumnIndex(GoodsDBHelper.GOODS_PRICE)));
                goods.setUnit(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_UNIT)));
                goods.setFreight(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FREIGHT)));
                goods.setAddress(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_ADDRESS)));
                goods.setText(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TEXT)));
                goods.setPublisher(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_PUBLISHER)));
                goods.setState(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_STATE)));
                goodsList.add(goods);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return goodsList ;
    }
//通过Id查询商品
    @SuppressLint("Range")
    public Goods getGoodsById(int id){
        SQLiteDatabase db = goodsDBHelper.getReadableDatabase() ;
        Cursor cursor = db.query(GoodsDBHelper.TABLE_GOODS , new String[]{GoodsDBHelper.GOODS_ID , GoodsDBHelper.GOODS_NAME ,
                GoodsDBHelper.GOODS_FIRST_IMG , GoodsDBHelper.GOODS_IMG_LIST , GoodsDBHelper.GOODS_TYPE ,
                        GoodsDBHelper.GOODS_PRICE , GoodsDBHelper.GOODS_UNIT , GoodsDBHelper.GOODS_FREIGHT ,
                        GoodsDBHelper.GOODS_ADDRESS , GoodsDBHelper.GOODS_TEXT ,
                GoodsDBHelper.GOODS_PUBLISHER , GoodsDBHelper.GOODS_STATE} , GoodsDBHelper.GOODS_ID + " = ? " ,
                new String[]{String.valueOf(id)} , null , null , null ,null);
        Goods goods = null ;
        if (cursor!= null && cursor.moveToFirst()){
            goods = new Goods(
                    cursor.getInt(cursor.getColumnIndex(GoodsDBHelper.GOODS_ID)),
                    cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_NAME)),
                    cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FIRST_IMG)),
                    cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_IMG_LIST)),
                    cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TYPE)),
                    cursor.getDouble(cursor.getColumnIndex(GoodsDBHelper.GOODS_PRICE)),
                    cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_UNIT)),
                    cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FREIGHT)),
                    cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_ADDRESS)),
                    cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TEXT)),
                    cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_PUBLISHER)),
                    cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_STATE))
            );
            cursor.close();
        }
        return goods ;
    }
    //通过类型查询list商品
    @SuppressLint("Range")
    public List<Goods> getGoodsByType(String type){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Goods> goodsList = new ArrayList<>() ;
        try {
            db = goodsDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + GoodsDBHelper.TABLE_GOODS + " WHERE " +
                    GoodsDBHelper.GOODS_TYPE + " = ? AND " + GoodsDBHelper.GOODS_STATE + " = '上架中' ORDER BY "
                    + GoodsDBHelper.GOODS_ID + " DESC " ;
            cursor = db.rawQuery(query , new String[]{type});
            while (cursor.moveToNext()){
                 Goods goods = new Goods() ;
                 goods.setId(cursor.getInt(cursor.getColumnIndex(GoodsDBHelper.GOODS_ID)));
                goods.setName(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_NAME)));
                goods.setFirst_image(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FIRST_IMG)));
                goods.setImg_list(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_IMG_LIST)));
                goods.setType(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TYPE)));
                goods.setPrice(cursor.getDouble(cursor.getColumnIndex(GoodsDBHelper.GOODS_PRICE)));
                goods.setUnit(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_UNIT)));
                goods.setFreight(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FREIGHT)));
                goods.setAddress(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_ADDRESS)));
                goods.setText(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TEXT)));
                goods.setPublisher(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_PUBLISHER)));
                goods.setState(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_STATE)));
                goodsList.add(goods);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return goodsList ;
    }
//搜索商品,通过搜索输入商品的名称和类型进行搜索
@SuppressLint("Range")
public List<Goods> searchGoodsByName(String input) {
    SQLiteDatabase db = null;
    Cursor cursor = null;
    List<Goods> goodsList = new ArrayList<>();
    try {
        db = goodsDBHelper.getReadableDatabase();

        // 构建LIKE条件以匹配任意字符
        StringBuilder likeConditions = new StringBuilder();
        for (int i = 0; i < input.length(); i++) {
            if (i > 0) {
                likeConditions.append(" OR ");
            }
            likeConditions.append(GoodsDBHelper.GOODS_NAME).append(" LIKE '%").append(input.charAt(i)).append("%'");
        }

        String query = "SELECT * FROM " + GoodsDBHelper.TABLE_GOODS +
                " WHERE (" + likeConditions.toString() + ") AND " + GoodsDBHelper.GOODS_STATE + " = '上架中'" +
                " ORDER BY " + GoodsDBHelper.GOODS_ID + " DESC ";

        cursor = db.rawQuery(query, null);

        while (cursor.moveToNext()) {
            Goods goods = new Goods();
            goods.setId(cursor.getInt(cursor.getColumnIndex(GoodsDBHelper.GOODS_ID)));
            goods.setName(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_NAME)));
            goods.setFirst_image(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FIRST_IMG)));
            goods.setImg_list(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_IMG_LIST)));
            goods.setType(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TYPE)));
            goods.setPrice(cursor.getDouble(cursor.getColumnIndex(GoodsDBHelper.GOODS_PRICE)));
            goods.setUnit(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_UNIT)));
            goods.setFreight(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_FREIGHT)));
            goods.setAddress(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_ADDRESS)));
            goods.setText(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_TEXT)));
            goods.setState(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_STATE)));
            goods.setPublisher(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_PUBLISHER)));
            goods.setState(cursor.getString(cursor.getColumnIndex(GoodsDBHelper.GOODS_STATE)));
            goodsList.add(goods);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (cursor != null) {
            cursor.close();
        }
        if (db != null) {
            db.close();
        }
    }
    return goodsList;
}


    //以下是关于动态表的操作方法
    //发布动态
    public boolean addTrends(String text , String img_list , String publisher){
        SQLiteDatabase db = trendsDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(TrendsDBHelper.TRENDS_TEXT , text);
        values.put(TrendsDBHelper.TRENDS_IMG_LIST , img_list);
        values.put(TrendsDBHelper.TRENDS_PUBLISHER , publisher);
        long isAdd = db.insert(TrendsDBHelper.TABLE_TRENDS , null , values) ;
        return isAdd != -1 ;
    }
    public void deleteTrendsById(int id){
        SQLiteDatabase db = trendsDBHelper.getWritableDatabase();
        db.delete(TrendsDBHelper.TABLE_TRENDS , TrendsDBHelper.TRENDS_ID + " =? " , new String[]{String.valueOf(id)});
    }
    @SuppressLint("Range")
    public Trends getTrendsById(int id){
        SQLiteDatabase db = trendsDBHelper.getReadableDatabase();
        Cursor cursor = db.query(TrendsDBHelper.TABLE_TRENDS , new String[]{
                TrendsDBHelper.TRENDS_ID , TrendsDBHelper.TRENDS_TEXT , TrendsDBHelper.TRENDS_IMG_LIST ,
                TrendsDBHelper.TRENDS_PUBLISHER
        },TrendsDBHelper.TRENDS_ID + " =? " , new String[]{String.valueOf(id)} , null , null,null,null);
        Trends trends = null ;
        if (cursor != null){
            trends = new Trends(
                    cursor.getInt(cursor.getColumnIndex(TrendsDBHelper.TRENDS_ID)),
                    cursor.getString(cursor.getColumnIndex(TrendsDBHelper.TRENDS_TEXT)),
                    cursor.getString(cursor.getColumnIndex(TrendsDBHelper.TRENDS_IMG_LIST)),
                    cursor.getString(cursor.getColumnIndex(TrendsDBHelper.TRENDS_PUBLISHER))
            );
            cursor.close();
        }
        return trends ;
    }

    @SuppressLint("Range")
    public List<Trends> getTrends(){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Trends> trendsList = new ArrayList<>();
        try{
            db = trendsDBHelper.getReadableDatabase();
            String query = "SELECT * FROM " + TrendsDBHelper.TABLE_TRENDS + " ORDER BY " +
                    TrendsDBHelper.TRENDS_ID + " DESC " ;
            cursor = db.rawQuery(query , null);
            while (cursor.moveToNext()){
                Trends trends = new Trends();
                trends.setId(cursor.getInt(cursor.getColumnIndex(TrendsDBHelper.TRENDS_ID)));
                trends.setText(cursor.getString(cursor.getColumnIndex(TrendsDBHelper.TRENDS_TEXT)));
                trends.setImg_list(cursor.getString(cursor.getColumnIndex(TrendsDBHelper.TRENDS_IMG_LIST)));
                trends.setPublisher(cursor.getString(cursor.getColumnIndex(TrendsDBHelper.TRENDS_PUBLISHER)));
                trendsList.add(trends);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return trendsList ;
    }


    @SuppressLint("Range")
    public List<Trends> getTrendsByUser(String user){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Trends> trendsList = new ArrayList<>();
        try{
            db = trendsDBHelper.getReadableDatabase();
            String query = "SELECT * FROM " + TrendsDBHelper.TABLE_TRENDS + " WHERE " +
                    TrendsDBHelper.TRENDS_PUBLISHER + " =? " + " ORDER BY " +
                    TrendsDBHelper.TRENDS_ID + " DESC " ;
            cursor = db.rawQuery(query , new String[]{user});
            while (cursor.moveToNext()){
                Trends trends = new Trends();
                trends.setId(cursor.getInt(cursor.getColumnIndex(TrendsDBHelper.TRENDS_ID)));
                trends.setText(cursor.getString(cursor.getColumnIndex(TrendsDBHelper.TRENDS_TEXT)));
                trends.setImg_list(cursor.getString(cursor.getColumnIndex(TrendsDBHelper.TRENDS_IMG_LIST)));
                trends.setPublisher(cursor.getString(cursor.getColumnIndex(TrendsDBHelper.TRENDS_PUBLISHER)));
                trendsList.add(trends);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return trendsList ;
    }


    //以下是关于反馈表的操作方法
    //提交反馈
    public boolean addFeedback(String text , String img_list , String publisher){
        SQLiteDatabase db = feedbackDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(FeedbackDBHelper.FEEDBACK_TEXT , text);
        values.put(FeedbackDBHelper.FEEDBACK_IMG_LIST , img_list);
        values.put(FeedbackDBHelper.FEEDBACK_PUBLISHER , publisher);
        long isAdd = db.insert(FeedbackDBHelper.TABLE_FEEDBACK , null , values) ;
        return isAdd != -1 ;
    }

    @SuppressLint("Range")
    public Feedback getFeedbackById(int id){
        SQLiteDatabase db = feedbackDBHelper.getReadableDatabase() ;
        Cursor cursor = db.query(FeedbackDBHelper.TABLE_FEEDBACK , new String[]{
                FeedbackDBHelper.FEEDBACK_ID , FeedbackDBHelper.FEEDBACK_TEXT , FeedbackDBHelper.FEEDBACK_IMG_LIST ,
                FeedbackDBHelper.FEEDBACK_PUBLISHER
        } , FeedbackDBHelper.FEEDBACK_ID + " =? " , new String[]{String.valueOf(id)} , null,null,null,null);
        Feedback feedback = null ;
        if (cursor != null){
            feedback = new Feedback(
                    cursor.getInt(cursor.getColumnIndex(FeedbackDBHelper.FEEDBACK_ID)),
                    cursor.getString(cursor.getColumnIndex(FeedbackDBHelper.FEEDBACK_TEXT)),
                    cursor.getString(cursor.getColumnIndex(FeedbackDBHelper.FEEDBACK_IMG_LIST)),
                    cursor.getString(cursor.getColumnIndex(FeedbackDBHelper.FEEDBACK_PUBLISHER))
            );
            cursor.close();
        }
        return feedback ;
    }


    @SuppressLint("Range")
    public List<Feedback> getFeedbackByUser(String user){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Feedback> feedbackList = new ArrayList<>();
        try{
            db = feedbackDBHelper.getReadableDatabase();
            String query = "SELECT * FROM " + FeedbackDBHelper.TABLE_FEEDBACK + " WHERE " +
                    FeedbackDBHelper.FEEDBACK_PUBLISHER + " =? " + " ORDER BY " +
                    FeedbackDBHelper.FEEDBACK_ID + " DESC " ;
            cursor = db.rawQuery(query , new String[]{user});
            while (cursor.moveToNext()){
                Feedback feedback = new Feedback();
                feedback.setId(cursor.getInt(cursor.getColumnIndex(FeedbackDBHelper.FEEDBACK_ID)));
                feedback.setText(cursor.getString(cursor.getColumnIndex(FeedbackDBHelper.FEEDBACK_TEXT)));
                feedback.setImg_list(cursor.getString(cursor.getColumnIndex(FeedbackDBHelper.FEEDBACK_IMG_LIST)));
                feedback.setPublisher(cursor.getString(cursor.getColumnIndex(FeedbackDBHelper.FEEDBACK_PUBLISHER)));
                feedbackList.add(feedback);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return feedbackList;
    }

    //以下是关于通讯表的操作方法
    //添加成员
    public void addNewsletter(String phone1 , String phone2 , String time){
        SQLiteDatabase db  = newsletterDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(NewsletterDBHelper.NEWSLETTER_A , phone1);
        values.put(NewsletterDBHelper.NEWSLETTER_B , phone2);
        values.put(NewsletterDBHelper.NEWSLETTER_TIME , time);
        db.insert(NewsletterDBHelper.TABLE_NEWSLETTER , null , values);
    }

    @SuppressLint("Range")
    public NewsLetter getNewsletterByPhone(String phone1 , String phone2){
        SQLiteDatabase db = newsletterDBHelper.getReadableDatabase() ;
        Cursor cursor = db.query(NewsletterDBHelper.TABLE_NEWSLETTER , new String[]{
                NewsletterDBHelper.NEWSLETTER_ID , NewsletterDBHelper.NEWSLETTER_A , NewsletterDBHelper.NEWSLETTER_B ,
                NewsletterDBHelper.NEWSLETTER_TIME} , NewsletterDBHelper.NEWSLETTER_A + " = ? AND " +
                NewsletterDBHelper.NEWSLETTER_B + " = ? " , new String[]{phone1,phone2} ,
                null ,null , null ,null);
        NewsLetter newsLetter = null ;
        if (cursor != null && cursor.moveToFirst()){
            newsLetter = new NewsLetter(
                    cursor.getInt(cursor.getColumnIndex(NewsletterDBHelper.NEWSLETTER_ID)),
                    cursor.getString(cursor.getColumnIndex(NewsletterDBHelper.NEWSLETTER_A)),
                    cursor.getString(cursor.getColumnIndex(NewsletterDBHelper.NEWSLETTER_B)),
                    cursor.getString(cursor.getColumnIndex(NewsletterDBHelper.NEWSLETTER_TIME))
            );
            cursor.close();
        }
        return newsLetter ;
    }

    public void updateNewsletterTime(String phone1 , String phone2 , String time){
        SQLiteDatabase db = newsletterDBHelper.getWritableDatabase();
        ContentValues values = new ContentValues() ;
        values.put(NewsletterDBHelper.NEWSLETTER_TIME , time);
        String selection = NewsletterDBHelper.NEWSLETTER_A + " = ? AND " + NewsletterDBHelper.NEWSLETTER_B + " = ? ";
        db.update(NewsletterDBHelper.TABLE_NEWSLETTER , values, selection , new String[]{phone1,phone2});
    }

    @SuppressLint("Range")
    public List<NewsLetter> getNewsLetterByPhone(String phone){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<NewsLetter> newsLetterList = new ArrayList<>() ;
        try {
            db = newsletterDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + NewsletterDBHelper.TABLE_NEWSLETTER +
            " WHERE " + NewsletterDBHelper.NEWSLETTER_A + " = ? OR " + NewsletterDBHelper.NEWSLETTER_B + " = ? ORDER BY " +
            NewsletterDBHelper.NEWSLETTER_TIME + " DESC " ;
            cursor = db.rawQuery(query , new String[]{phone , phone});
            while (cursor.moveToNext()){
                NewsLetter newsLetter = new NewsLetter();
                newsLetter.setId(cursor.getInt(cursor.getColumnIndex(NewsletterDBHelper.NEWSLETTER_ID)));
                newsLetter.setPhone1(cursor.getString(cursor.getColumnIndex(NewsletterDBHelper.NEWSLETTER_A)));
                newsLetter.setPhone2(cursor.getString(cursor.getColumnIndex(NewsletterDBHelper.NEWSLETTER_B)));
                newsLetter.setTime(cursor.getString(cursor.getColumnIndex(NewsletterDBHelper.NEWSLETTER_TIME)));
                newsLetterList.add(newsLetter);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return newsLetterList ;
    }

    //以下是信息表的操作方法
    public void addMessage(String sendId , String receiveId , String text , String time){
        SQLiteDatabase db = newsletterDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(NewsletterDBHelper.MESSAGE_SEND , sendId);
        values.put(NewsletterDBHelper.MESSAGE_RECEIVE , receiveId);
        values.put(NewsletterDBHelper.MESSAGE_TEXT , text);
        values.put(NewsletterDBHelper.MESSAGE_TIME , time);
        db.insert(NewsletterDBHelper.TABLE_MESSAGE , null , values) ;
    }

    @SuppressLint("Range")
    public List<Message> getMessage(){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Message> messageList = new ArrayList<>();
        try {
            db = newsletterDBHelper.getWritableDatabase();
            String query = "SELECT * FROM " + NewsletterDBHelper.TABLE_MESSAGE ;
            cursor = db.rawQuery(query , null);
            while (cursor.moveToNext()){
                Message message = new Message();
                message.setSend_id(cursor.getString(cursor.getColumnIndex(NewsletterDBHelper.MESSAGE_SEND)));
                message.setReceive_id(cursor.getString(cursor.getColumnIndex(NewsletterDBHelper.MESSAGE_RECEIVE)));
                message.setText(cursor.getString(cursor.getColumnIndex(NewsletterDBHelper.MESSAGE_TEXT)));
                message.setTime(cursor.getString(cursor.getColumnIndex(NewsletterDBHelper.MESSAGE_TIME)));
                messageList.add(message);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return messageList ;
    }



    ////以下是关于订单表的操作方法
    public boolean addOrder(int localId , int goodsId , int num , String remark , String buyer ,
                         String sales , String type , String time){
        SQLiteDatabase db = orderDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(OrderDBHelper.ORDER_LOCAL_ID , localId);
        values.put(OrderDBHelper.ORDER_GOODS_ID , goodsId);
        values.put(OrderDBHelper.ORDER_NUM , num);
        values.put(OrderDBHelper.ORDER_REMARK , remark);
        values.put(OrderDBHelper.ORDER_BUYER , buyer);
        values.put(OrderDBHelper.ORDER_SALES , sales);
        values.put(OrderDBHelper.ORDER_TYPE , type);
        values.put(OrderDBHelper.ORDER_TIME , time);
        long isAdd = db.insert(OrderDBHelper.TABLE_ORDER , null , values);
        return isAdd != -1 ;
    }

    //查询购买
    @SuppressLint("Range")
    public List<Order> getAllOrderBuy(String phone){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Order> orderList = new ArrayList<>() ;
        try {
            db = orderDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + OrderDBHelper.TABLE_ORDER + " WHERE " +
                    OrderDBHelper.ORDER_BUYER + " = ? ORDER BY " + OrderDBHelper.ORDER_TIME + " DESC" ;
            cursor = db.rawQuery(query , new String[]{phone});
            while (cursor.moveToNext()){
                Order order = new Order() ;
                order.setId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_ID)));
                order.setLocalId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_LOCAL_ID)));
                order.setGoodsId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_GOODS_ID)));
                order.setNum(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_NUM)));
                order.setRemark(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_REMARK)));
                order.setBuyer(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_BUYER)));
                order.setSales(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_SALES)));
                order.setType(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_TYPE)));
                order.setTime(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_TIME)));
                orderList.add(order);
            }
        }catch (Exception e){
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return orderList ;
    }

    @SuppressLint("Range")
    public List<Order> getOrderBuy(String phone , String type){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Order> orderList = new ArrayList<>() ;
        try {
            db = orderDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + OrderDBHelper.TABLE_ORDER + " WHERE " +
                    OrderDBHelper.ORDER_BUYER + " = ? AND " + OrderDBHelper.ORDER_TYPE + " = ? ORDER BY " + OrderDBHelper.ORDER_TIME + " DESC" ;
            cursor = db.rawQuery(query , new String[]{phone , type});
            while (cursor.moveToNext()){
                Order order = new Order() ;
                order.setId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_ID)));
                order.setLocalId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_LOCAL_ID)));
                order.setGoodsId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_GOODS_ID)));
                order.setNum(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_NUM)));
                order.setRemark(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_REMARK)));
                order.setBuyer(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_BUYER)));
                order.setSales(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_SALES)));
                order.setType(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_TYPE)));
                order.setTime(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_TIME)));
                orderList.add(order);
            }
        }catch (Exception e){
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return orderList ;
    }
    //查询出售
    @SuppressLint("Range")
    public List<Order> getAllOrderSales(String phone){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Order> orderList = new ArrayList<>() ;
        try {
            db = orderDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + OrderDBHelper.TABLE_ORDER + " WHERE " +
                    OrderDBHelper.ORDER_SALES + " = ? ORDER BY " + OrderDBHelper.ORDER_TIME + " DESC" ;
            cursor = db.rawQuery(query , new String[]{phone});
            while (cursor.moveToNext()){
                Order order = new Order() ;
                order.setId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_ID)));
                order.setLocalId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_LOCAL_ID)));
                order.setGoodsId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_GOODS_ID)));
                order.setNum(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_NUM)));
                order.setRemark(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_REMARK)));
                order.setBuyer(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_BUYER)));
                order.setSales(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_SALES)));
                order.setType(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_TYPE)));
                order.setTime(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_TIME)));
                orderList.add(order);
            }
        }catch (Exception e){
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return orderList ;
    }
    @SuppressLint("Range")
    public List<Order> getOrderSales(String phone , String type){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Order> orderList = new ArrayList<>() ;
        try {
            db = orderDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + OrderDBHelper.TABLE_ORDER + " WHERE " +
                    OrderDBHelper.ORDER_SALES + " = ? AND " + OrderDBHelper.ORDER_TYPE + " = ? ORDER BY "
                    + OrderDBHelper.ORDER_TIME + " DESC" ;
            cursor = db.rawQuery(query , new String[]{phone , type});
            while (cursor.moveToNext()){
                Order order = new Order() ;
                order.setId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_ID)));
                order.setLocalId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_LOCAL_ID)));
                order.setGoodsId(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_GOODS_ID)));
                order.setNum(cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_NUM)));
                order.setRemark(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_REMARK)));
                order.setBuyer(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_BUYER)));
                order.setSales(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_SALES)));
                order.setType(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_TYPE)));
                order.setTime(cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_TIME)));
                orderList.add(order);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return orderList ;
    }
    //更改状态
    public void updateOrderType(int id , String type , String time){
        SQLiteDatabase db = orderDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(OrderDBHelper.ORDER_TYPE , type);
        values.put(OrderDBHelper.ORDER_TIME , time);
        String selection = OrderDBHelper.ORDER_ID + " = ?";
        db.update(OrderDBHelper.TABLE_ORDER , values , selection , new String[]{String.valueOf(id)});
    }
    //查询单条订单
    @SuppressLint("Range")
    public Order getOrderById(int id){
        SQLiteDatabase db = orderDBHelper.getReadableDatabase() ;
        Cursor cursor = db.query(OrderDBHelper.TABLE_ORDER , new String[]{OrderDBHelper.ORDER_ID ,
                OrderDBHelper.ORDER_LOCAL_ID ,
        OrderDBHelper.ORDER_GOODS_ID , OrderDBHelper.ORDER_NUM , OrderDBHelper.ORDER_REMARK ,
        OrderDBHelper.ORDER_BUYER , OrderDBHelper.ORDER_SALES , OrderDBHelper.ORDER_TYPE , OrderDBHelper.ORDER_TIME},
                OrderDBHelper.ORDER_ID + " =?", new String[]{String.valueOf(id)} ,
                null ,null ,null,null);
        Order order = null ;
        if (cursor != null && cursor.moveToFirst()){
            order = new Order(
                    cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_ID)),
                    cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_LOCAL_ID)),
                    cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_GOODS_ID)),
                    cursor.getInt(cursor.getColumnIndex(OrderDBHelper.ORDER_NUM)),
                    cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_REMARK)),
                    cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_BUYER)),
                    cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_SALES)),
                    cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_TYPE)),
                    cursor.getString(cursor.getColumnIndex(OrderDBHelper.ORDER_TIME))
            );
            cursor.close();
        }
        return order ;
    }



    //以下时收藏表的操作方法
    //添加收藏，收藏表中有三种类型/goods/store/news
    public void addCollect(String gId ,String type , String user){
        SQLiteDatabase db = collectDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues();
        values.put(CollectDBHelper.COLLECT_GID , gId);
        values.put(CollectDBHelper.COLLECT_TYPE , type);
        values.put(CollectDBHelper.COLLECT_USER , user);
        db.insert(CollectDBHelper.TABLE_COLLECT , null , values) ;
    }
    //删除收藏
    public void delCollect(int id){
        SQLiteDatabase db = collectDBHelper.getWritableDatabase() ;
        db.delete(CollectDBHelper.TABLE_COLLECT ,
                CollectDBHelper.COLLECT_ID + " = ? " , new String[]{String.valueOf(id)});
    }
    //查询收藏
    @SuppressLint("Range")
    public Collect getCollect(String gId , String type , String user){
        SQLiteDatabase db = collectDBHelper.getReadableDatabase() ;
        Cursor cursor = db.query(CollectDBHelper.TABLE_COLLECT , new String[]{
                CollectDBHelper.COLLECT_ID , CollectDBHelper.COLLECT_GID ,
                CollectDBHelper.COLLECT_TYPE , CollectDBHelper.COLLECT_USER} ,
                CollectDBHelper.COLLECT_GID + " = ? AND " +
                CollectDBHelper.COLLECT_TYPE + " = ? AND " +
                CollectDBHelper.COLLECT_USER + " = ? " ,
                new String[]{gId,type,user},null,null,null,null);
        Collect collect = null ;
        if (cursor != null && cursor.moveToFirst()){
            collect = new Collect(
                    cursor.getInt(cursor.getColumnIndex(CollectDBHelper.COLLECT_ID)),
                    cursor.getString(cursor.getColumnIndex(CollectDBHelper.COLLECT_GID)),
                    cursor.getString(cursor.getColumnIndex(CollectDBHelper.COLLECT_TYPE)),
                    cursor.getString(cursor.getColumnIndex(CollectDBHelper.COLLECT_USER))
            );
            cursor.close();
        }
        return collect ;
    }

    @SuppressLint("Range")
    public Collect getCollectById(int id){
        SQLiteDatabase db = collectDBHelper.getReadableDatabase() ;
        Cursor cursor = db.query(CollectDBHelper.TABLE_COLLECT , new String[]{
                        CollectDBHelper.COLLECT_ID , CollectDBHelper.COLLECT_GID ,
                        CollectDBHelper.COLLECT_TYPE , CollectDBHelper.COLLECT_USER} ,
                CollectDBHelper.COLLECT_ID + " = ? " ,
                new String[]{String.valueOf(id)},null,null,null,null);
        Collect collect = null ;
        if (cursor != null && cursor.moveToFirst()){
            collect = new Collect(
                    cursor.getInt(cursor.getColumnIndex(CollectDBHelper.COLLECT_ID)),
                    cursor.getString(cursor.getColumnIndex(CollectDBHelper.COLLECT_GID)),
                    cursor.getString(cursor.getColumnIndex(CollectDBHelper.COLLECT_TYPE)),
                    cursor.getString(cursor.getColumnIndex(CollectDBHelper.COLLECT_USER))
            );
            cursor.close();
        }
        return collect ;
    }
    //查询收藏信息
    @SuppressLint("Range")
    public List<Collect> getCollect(String user , String type){
        SQLiteDatabase db = null ;
        Cursor cursor = null  ;
        List<Collect> collectList = new ArrayList<>();
        try {
            db = collectDBHelper.getReadableDatabase() ;
            String query = "SELECT * FROM " + CollectDBHelper.TABLE_COLLECT + " WHERE "
                    + CollectDBHelper.COLLECT_USER + " = ? AND " +
                    CollectDBHelper.COLLECT_TYPE + " = ? ORDER BY " +
                    CollectDBHelper.COLLECT_ID + " DESC " ;
            cursor = db.rawQuery(query , new String[]{user , type});
            while (cursor.moveToNext()){
                Collect collect = new Collect() ;
                collect.setId(cursor.getInt(cursor.getColumnIndex(CollectDBHelper.COLLECT_ID)));
                collect.setgID(cursor.getString(cursor.getColumnIndex(CollectDBHelper.COLLECT_GID)));
                collect.setType(cursor.getString(cursor.getColumnIndex(CollectDBHelper.COLLECT_TYPE)));
                collect.setUser(cursor.getString(cursor.getColumnIndex(CollectDBHelper.COLLECT_USER)));
                collectList.add(collect);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return collectList ;
    }




    //以下是关于购物车的操作方法
    //添加购物车
    public void addCart(int goodsId , int num , String user){
        SQLiteDatabase db = cartDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues();
        values.put(CartDBHelper.CART_GOODS_ID , goodsId);
        values.put(CartDBHelper.CART_GOODS_NUM , num);
        values.put(CartDBHelper.CART_USER , user);
        db.insert(CartDBHelper.TABLE_CART , null , values);
    }
    public void updateCartNumById(int id , int num){
        SQLiteDatabase db = cartDBHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(CartDBHelper.CART_GOODS_NUM , num);
        String selection = CartDBHelper.CART_ID + " = ? " ;
        db.update(CartDBHelper.TABLE_CART , values , selection , new String[]{String.valueOf(id)});
    }
    public void deleteCartById(int id){
        SQLiteDatabase db = cartDBHelper.getWritableDatabase();
        db.delete(CartDBHelper.TABLE_CART , CartDBHelper.CART_ID + " = ?" ,
                new String[]{String.valueOf(id)});
    }
    @SuppressLint("Range")
    public Cart getCartById(int id){
        SQLiteDatabase db = cartDBHelper.getReadableDatabase() ;
        Cursor cursor = db.query(CartDBHelper.TABLE_CART , new String[]{
        CartDBHelper.CART_ID , CartDBHelper.CART_GOODS_ID , CartDBHelper.CART_GOODS_NUM , CartDBHelper.CART_USER},
                CartDBHelper.CART_ID + " =? " , new String[]{String.valueOf(id)} ,
                null , null, null,null
        );
        Cart cart = null ;
        if (cursor != null && cursor.moveToFirst()){
            cart = new Cart(
                    cursor.getInt(cursor.getColumnIndex(CartDBHelper.CART_ID)),
                    cursor.getInt(cursor.getColumnIndex(CartDBHelper.CART_GOODS_ID)),
                    cursor.getInt(cursor.getColumnIndex(CartDBHelper.CART_GOODS_NUM)),
                    cursor.getString(cursor.getColumnIndex(CartDBHelper.CART_USER))
            );
            cursor.close();
        }
        return cart ;
    }

    @SuppressLint("Range")
    public List<Cart> getCartByPhone(String user){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Cart> cartList = new ArrayList<>();
        try {
            db = cartDBHelper.getReadableDatabase();
            String query = "SELECT * FROM " + CartDBHelper.TABLE_CART + " WHERE " +
                    CartDBHelper.CART_USER + " =? ORDER BY " +
                    CartDBHelper.CART_ID + " DESC " ;
            cursor = db.rawQuery(query , new String[]{user});
            while (cursor.moveToNext()){
                Cart cart = new Cart();
                cart.setId(cursor.getInt(cursor.getColumnIndex(CartDBHelper.CART_ID)));
                cart.setGoodsId(cursor.getInt(cursor.getColumnIndex(CartDBHelper.CART_GOODS_ID)));
                cart.setNum(cursor.getInt(cursor.getColumnIndex(CartDBHelper.CART_GOODS_NUM)));
                cart.setUser(cursor.getString(cursor.getColumnIndex(CartDBHelper.CART_USER)));
                cartList.add(cart);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return cartList ;
    }

    //以下是关于足迹表的操作方法
    public void addFoot(int goodsId , String user ,String time){
        SQLiteDatabase db = footDBHelper.getWritableDatabase() ;
        ContentValues values = new ContentValues() ;
        values.put(FootDBHelper.FOOT_GOODS , goodsId);
        values.put(FootDBHelper.FOOT_USER , user);
        values.put(FootDBHelper.FOOT_TIME , time);
        db.insert(FootDBHelper.TABLE_FOOT , null , values);
    }
    @SuppressLint("Range")
    public Foot getFoot(int goodsId, String user){
        SQLiteDatabase db = footDBHelper.getReadableDatabase();
        Cursor cursor = db.query(FootDBHelper.TABLE_FOOT , new String[]{FootDBHelper.FOOT_ID , FootDBHelper.FOOT_GOODS ,
        FootDBHelper.FOOT_USER , FootDBHelper.FOOT_TIME} , FootDBHelper.FOOT_GOODS + " =? AND " +
                FootDBHelper.FOOT_USER + " =? " , new String[]{String.valueOf(goodsId),user},null,null,null,null);
        Foot foot = null ;
        if (cursor != null && cursor.moveToFirst()){
            foot = new Foot(
                    cursor.getInt(cursor.getColumnIndex(FootDBHelper.FOOT_ID)),
                    cursor.getInt(cursor.getColumnIndex(FootDBHelper.FOOT_GOODS)),
                    cursor.getString(cursor.getColumnIndex(FootDBHelper.FOOT_USER)),
                    cursor.getString(cursor.getColumnIndex(FootDBHelper.FOOT_TIME))
            );
            cursor.close();
        }
        return foot ;
    }
    public void deleteFoot(int goodsId , String user){
        SQLiteDatabase db = footDBHelper.getWritableDatabase() ;
        db.delete(FootDBHelper.TABLE_FOOT , FootDBHelper.FOOT_GOODS + " =? AND " +
                FootDBHelper.FOOT_USER + " =? " , new String[]{String.valueOf(goodsId),user});
    }
    @SuppressLint("Range")
    public List<Foot> getFoots(String user){
        SQLiteDatabase db = null ;
        Cursor cursor = null ;
        List<Foot> footList = new ArrayList<>();
        try {
            db = footDBHelper.getReadableDatabase();
            String query = "SELECT * FROM " + FootDBHelper.TABLE_FOOT + " WHERE " +
                    FootDBHelper.FOOT_USER + " =? ORDER BY " +
                    FootDBHelper.FOOT_TIME + " DESC " ;
            cursor = db.rawQuery(query , new String[]{user});
            while (cursor.moveToNext()){
                Foot foot = new Foot();
                foot.setId(cursor.getInt(cursor.getColumnIndex(FootDBHelper.FOOT_ID)));
                foot.setGoodsId(cursor.getInt(cursor.getColumnIndex(FootDBHelper.FOOT_GOODS)));
                foot.setUser(cursor.getString(cursor.getColumnIndex(FootDBHelper.FOOT_USER)));
                foot.setTime(cursor.getString(cursor.getColumnIndex(FootDBHelper.FOOT_TIME)));
                footList.add(foot);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (cursor != null){
                cursor.close();
            }
            if (db != null){
                db.close();
            }
        }
        return footList ;
    }

    public void addOrUpdateFoot(int goodsId , String user , String time){
        SQLiteDatabase db = footDBHelper.getReadableDatabase();
        SQLiteDatabase db2 = footDBHelper.getWritableDatabase();
        String[] selection = {String.valueOf(goodsId) , user};
        Cursor cursor = db.query(FootDBHelper.TABLE_FOOT , new String[]{String.valueOf(FootDBHelper.FOOT_ID)} ,
                FootDBHelper.FOOT_GOODS + " =? AND " + FootDBHelper.FOOT_USER + " =? " ,
                selection,null,null,null,null);
        if (cursor != null && cursor.moveToFirst()){
            String updateTime = "UPDATE " + FootDBHelper.TABLE_FOOT + " SET " + FootDBHelper.FOOT_TIME + " =? WHERE " +
                    FootDBHelper.FOOT_GOODS + " =? AND " + FootDBHelper.FOOT_USER + " =? ";
            db2.execSQL(updateTime,new Object[]{time,goodsId,user});
        }else {
            String getCount = "SELECT COUNT(*) FROM " + FootDBHelper.TABLE_FOOT + " WHERE " + FootDBHelper.FOOT_USER +
                    " =? ";
            Cursor countCursor = db.rawQuery(getCount,new String[]{user});
            if (countCursor != null && countCursor.moveToFirst()){
                int count = countCursor.getInt(0);
                if (count>30){
                    String deleteSql = "DELETE FROM " + FootDBHelper.TABLE_FOOT + " WHERE " +
                            FootDBHelper.FOOT_USER + " =? ORDER BY " +
                            FootDBHelper.FOOT_TIME + " ASC LIMIT 1 ";
                    db2.execSQL(deleteSql,new Object[]{user});
                }
            }
            if (countCursor != null){
                countCursor.close();
            }
            String insertSql = "INSERT INTO " + FootDBHelper.TABLE_FOOT + " (" +
                    FootDBHelper.FOOT_GOODS + "," +
                    FootDBHelper.FOOT_USER + "," +
                    FootDBHelper.FOOT_TIME + ")" +
                    " VALUES (?,?,?)";
            db2.execSQL(insertSql,new Object[]{goodsId,user,time});
        }
        if (cursor != null){
            cursor.close();
        }
    }
}
